﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;

namespace IndexDirector
{
    /// <summary>
    /// Interaction logic for Window1.xaml
    /// </summary>
    public partial class Window1 : Window
    {
        public Window1()
        {
            InitializeComponent();

            SqlConnection SQLServerConnection = new SqlConnection(BuildConnectionString("SQL", "justin", "B00ling", "SVCC", false));

            dgdMissingIndexes.DataContext = GetAllSQLJobs(SQLServerConnection);
        }

        private static string BuildConnectionString(string p_DataSource, string p_UserName, string p_UserPassword, string p_DatabaseName, bool p_IsIntegrated)
        {
            if (p_IsIntegrated)
            {
                string ConnectionString = "Trusted_Connection=true;Application Name=Antimuon;";

                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);

                builder.DataSource = p_DataSource;
                builder.InitialCatalog = p_DatabaseName;

                return builder.ConnectionString;
            }
            else
            {
                string ConnectionString = "Trusted_Connection=false;Application Name=Antimuon;";

                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);

                builder.DataSource = p_DataSource;
                builder.InitialCatalog = p_DatabaseName;
                builder.UserID = p_UserName;
                builder.Password = p_UserPassword;

                return builder.ConnectionString;
            }


        }



        private DataTable GetAllSQLJobs(SqlConnection p_SQLServerConnection)
        {
            string sqlAllSQLJobs = "";
            sqlAllSQLJobs = sqlAllSQLJobs + "USE msdb;	";
            sqlAllSQLJobs = sqlAllSQLJobs + "SELECT mi.database_id, DatabaseName = db.name, mi.[object_id], TableName = mi.statement, ";
            sqlAllSQLJobs = sqlAllSQLJobs + "mi.equality_columns, mi.inequality_columns, mi.included_columns ";
            sqlAllSQLJobs = sqlAllSQLJobs + "FROM sys.dm_db_missing_index_details mi ";
            sqlAllSQLJobs = sqlAllSQLJobs + "INNER JOIN	sys.databases db ";
            sqlAllSQLJobs = sqlAllSQLJobs + "ON	db.database_id = mi.database_id	";
            sqlAllSQLJobs = sqlAllSQLJobs + "WHERE db.name != 'msdb' ";
            sqlAllSQLJobs = sqlAllSQLJobs + "ORDER BY TableName; ";
            SqlDataAdapter adapterAllSQLJobs = new SqlDataAdapter(sqlAllSQLJobs, p_SQLServerConnection);
            DataTable dtAllSQLJobs = new DataTable("dtAllSQLJobs");

            try
            {
                adapterAllSQLJobs.Fill(dtAllSQLJobs);
            }
            catch { }
            finally { p_SQLServerConnection.Close(); }

            return dtAllSQLJobs;

        }    
    
    }
}
